package sg.techstudio.suntec.dao;

import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.DateType;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import sg.techstudio.suntec.object.ContactDevice;
import sg.techstudio.suntec.object.DashboardRecordObj;
import sg.techstudio.suntec.object.MacSession;
import sg.techstudio.suntec.object.Radacct;
import sg.techstudio.suntec.object.Radcheck;

@Repository("radiusDao")
@Transactional
public class RadiusDao {
	private static final Log log = LogFactory.getLog(RadiusDao.class);
	private static final long MAXOCTET = 12345678901234L;
	
	@Autowired
	private Integer macSessionDurationInMinutes;
	
	@Autowired
	private SessionFactory sessionFactory;
	
	public void saveRadcheck(Radcheck r) {
		log.debug("saveRadcheck() entry");
		Session ses = sessionFactory.getCurrentSession();
		ses.saveOrUpdate(r);
	}
	
	@SuppressWarnings("unchecked")
	public Radacct getLastRadacct(ContactDevice cd) {
		log.debug("getLastRadacct() entry");
		if(cd==null)
			return null;
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radacct.class);
		Calendar now = Calendar.getInstance();
		now.add(Calendar.MINUTE, -macSessionDurationInMinutes);
		c.add(Restrictions.ilike("callingstationid", cd.getMac()));
		c.add(Restrictions.gt("radacctid", cd.getLastradacctid()));
		c.add(Restrictions.gt("acctstarttime", now.getTime()));
		c.addOrder(Order.desc("radacctid"));
		List<Radacct> list = c.list();
		return list==null||list.isEmpty()?null:list.get(0);
	}
	
	public void saveRadcheck(Collection<Radcheck> rCol) {
		log.debug("saveRadcheck() entry");
		if(rCol==null)
			return;
		Session ses = sessionFactory.getCurrentSession();
		for(Radcheck r: rCol)
			ses.saveOrUpdate(r);
	}
	
	public void deleteRadcheck(Collection<Radcheck> rCol) {
		log.debug("deleteRadcheck() entry");
		if(rCol==null)
			return;
		Session ses = sessionFactory.getCurrentSession();
		for(Radcheck r: rCol)
			ses.delete(r);
	}
	
	@SuppressWarnings("rawtypes")
	public void deleteRadcheckByUsername(Collection<String> rCol) {
		log.debug("deleteRadcheckByUsername() entry");
		if(rCol==null || rCol.isEmpty())
			return;
		Session ses = sessionFactory.getCurrentSession();
		Criteria c = ses.createCriteria(Radcheck.class);
		c.add(Restrictions.in("username", rCol));
		List res = c.list();
		if(res!=null)
			for(Object o: res)
				ses.delete(o);
	}
	
	public void updateRadcheck(Collection<Radcheck> rCol) {
		log.debug("updateRadcheck() entry");
		if(rCol==null)
			return;
		Session ses = sessionFactory.getCurrentSession();
		for(Radcheck r: rCol)
			ses.update(r);
	}
	
	@SuppressWarnings("unchecked")
	public List<Radcheck> getRadCheckByName(String username) {
		log.debug("getRadCheckByName() entry");
		if(username==null || username.isEmpty())
			return null;
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radcheck.class);
		c.add(Restrictions.eq("username", username));
		return c.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Radcheck> getRadCheckByName(Collection<String> username) {
		log.debug("getRadCheckByName() entry");
		if(username==null || username.isEmpty())
			return null;
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radcheck.class);
		c.add(Restrictions.in("username", username));
		return c.list();
	}
	
	public Long countActiveSession(String username, String mac) {
		log.debug("countActiveSession() entry. username: "+username+" mac:"+mac);
		if(username==null || username.isEmpty())
			return 0L;
//		log.debug("username:"+username+"; mac="+StringUtils.lowerCase(StringUtils.remove(mac, ":")));
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radacct.class);
		c.add(Restrictions.eq("username", username));
//		c.add(Restrictions.ne("callingstationid", StringUtils.lowerCase(StringUtils.remove(mac, ":")))); //exclude own mac
		c.add(Restrictions.isNull("acctstoptime"));
		c.setProjection(Projections.countDistinct("callingstationid"));
		Long ret = (Long) c.uniqueResult();
		log.debug("returning: "+ret);
		return ret;
	}
	
	@SuppressWarnings("unchecked")
	public void deleteInactiveSession(String username, int hourPast) {
		log.debug("deleteInactiveSession() entry");
		if(StringUtils.isBlank(username))
			return;
		hourPast = Math.max(0, hourPast);
		Calendar now = Calendar.getInstance();
		now.add(Calendar.HOUR, -hourPast);
		
		Session ses = sessionFactory.getCurrentSession();
		Criteria c = ses.createCriteria(Radacct.class);
		c.add(Restrictions.eq("username", username));
		c.add(Restrictions.isNull("acctstoptime"));
		c.add(Restrictions.le("acctstarttime", now.getTime()));
		List<Radacct> list = c.list();
		if(list==null||list.isEmpty())
			return;
		for(Radacct r: list) {
			r.setAcctstoptime(new Date());
			ses.update(r);
		}
	}
	
	public Long countRadacctList(String search, Radacct filter){
		log.debug("countRadacctList() entry");
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radacct.class);
		if(!StringUtils.isBlank(search)) {
			c.add(Restrictions.or(Restrictions.ilike("username", "%"+search+"%")
					, Restrictions.ilike("framedipaddress", "%"+search+"%")
					, Restrictions.ilike("callingstationid", "%"+search+"%")));
		}
		if(filter!=null) {
			if(!StringUtils.isBlank(filter.getUsername()))
				c.add(Restrictions.ilike("username", "%"+filter.getUsername()+"%"));
			if(!StringUtils.isBlank(filter.getFramedipaddress()))
				c.add(Restrictions.ilike("framedipaddress", "%"+filter.getFramedipaddress()+"%"));
			if(!StringUtils.isBlank(filter.getCallingstationid()))
				c.add(Restrictions.ilike("callingstationid", "%"+filter.getCallingstationid()+"%"));
			if(filter.getAcctstarttime()!=null)
				c.add(Restrictions.ge("acctstarttime", filter.getAcctstarttime()));
			if(filter.getAcctstoptime()!=null)
				c.add(Restrictions.le("acctstarttime", DateUtils.addDays(filter.getAcctstoptime(), 1)));
		}
		c.setProjection(Projections.rowCount());
		return (Long) c.uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	public List<Radacct> getRadacctList(String search, Radacct filter, int firstResult, int maxResults){
		log.debug("getRadacctList() entry");
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radacct.class);
		if(!StringUtils.isBlank(search)) {
			c.add(Restrictions.or(Restrictions.ilike("username", "%"+search+"%")
					, Restrictions.ilike("framedipaddress", "%"+search+"%")
					, Restrictions.ilike("callingstationid", "%"+search+"%")));
		}
		if(filter!=null) {
			if(!StringUtils.isBlank(filter.getUsername()))
				c.add(Restrictions.ilike("username", "%"+filter.getUsername()+"%"));
			if(!StringUtils.isBlank(filter.getFramedipaddress()))
				c.add(Restrictions.ilike("framedipaddress", "%"+filter.getFramedipaddress()+"%"));
			if(!StringUtils.isBlank(filter.getCallingstationid()))
				c.add(Restrictions.ilike("callingstationid", "%"+filter.getCallingstationid()+"%"));
			if(filter.getAcctstarttime()!=null)
				c.add(Restrictions.ge("acctstarttime", filter.getAcctstarttime()));
			if(filter.getAcctstoptime()!=null)
				c.add(Restrictions.le("acctstarttime", DateUtils.addDays(filter.getAcctstoptime(), 1)));
		}
		c.addOrder(Order.desc("radacctid"));
		c.setFirstResult(firstResult);
		c.setMaxResults(maxResults);
		return c.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<DashboardRecordObj> getRadacctGroupByDay() {
		log.debug("getRadacctGroupByDay() entry");
		Criteria c = sessionFactory.getCurrentSession().createCriteria(Radacct.class);
		Calendar cal = DateUtils.truncate(Calendar.getInstance(), Calendar.DATE);
		cal.add(Calendar.MONTH, -1);
		c.add(Restrictions.le("acctinputoctets", MAXOCTET));
		c.add(Restrictions.le("acctoutputoctets", MAXOCTET));
		c.add(Restrictions.ge("acctstarttime", cal.getTime()));
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.sqlGroupProjection(
				"date(acctstarttime) as acctstartdate1", "date(acctstarttime)"
				, new String[] {"acctstartdate1"}, new Type[] {new DateType()}));
		projectionList.add(Projections.rowCount());
		projectionList.add(Projections.sum("acctinputoctets"));
		projectionList.add(Projections.sum("acctoutputoctets"));
		c.setProjection(projectionList);
		List<Object[]> list = c.list();
		
		List<DashboardRecordObj> retList = new LinkedList<DashboardRecordObj>();
		Calendar now = Calendar.getInstance();
		cal.add(Calendar.DATE, 1); //skip 1
		while(cal.before(now)) {
			DashboardRecordObj dro = new DashboardRecordObj();
			dro.setRecordDate(cal.getTime());
			
			if(list!=null)
				for(Object[] o: list) {
					int i=0;
					 Date d = new Date(((java.sql.Date)o[i++]).getTime());
					 if(!DateUtils.isSameDay(d, cal.getTime()))
						 continue;
					 dro.setSessionCount((Long)o[i++]);
					 dro.setInputOctet((Long)o[i++]);
					 dro.setOutputOctet((Long)o[i++]);
					 break;
				}
			
			retList.add(dro);
			cal.add(Calendar.DATE, 1);
		}
		
		return retList;
	}
	
	@SuppressWarnings("unchecked")
	public MacSession getMacSession(String mac) {
		log.debug("getMacSession() entry");
		Criteria c = sessionFactory.getCurrentSession().createCriteria(MacSession.class);
		c.add(Restrictions.eq("mac", mac));
		c.addOrder(Order.desc("updatedDate"));
		List<MacSession> list = c.list();
		return list==null || list.isEmpty() ? null: list.get(0);
	}
	
	public void saveMacSession(MacSession macSession) {
		log.debug("saveMacSession() entry");
		if(macSession==null)
			return;
		Session ses = sessionFactory.getCurrentSession();
		macSession.setUpdatedDate(Calendar.getInstance().getTime());
		ses.saveOrUpdate(macSession);
	}
	
	@SuppressWarnings("unchecked")
	public void clearOldMacSession() {
		log.debug("clearOldMacSession() entry");
		Calendar now = Calendar.getInstance();
		now.add(Calendar.MINUTE, -macSessionDurationInMinutes);
		
		Session ses = sessionFactory.getCurrentSession();
		Criteria c = ses.createCriteria(MacSession.class);
		c.add(Restrictions.or(Restrictions.le("updatedDate", now.getTime())
				, Restrictions.le("endDate", Calendar.getInstance().getTime())));
		List<MacSession> list = c.list();
		if(list==null||list.isEmpty())
			return;
		for(MacSession ms: list) {
			ses.delete(ms);
		}
	}
	
}
